﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: February 16, 2016
-- Description:	Returns a table of the EntityID and the result of executing the derived attribute named Lookup
-- =============================================
CREATE PROCEDURE [dbo].[_ViewLookupEntities] 
	-- RequestorEntityID - The ID of the entity requesting the access
	-- at the moment this is only the personid, but it could be any entity in the system
	@RequestorEntityID int,
	@OrderBy nvarchar(1000) = NULL
AS
BEGIN
	CREATE TABLE #TempTable  
	(EntityID int, 
	 LookupEntityValue nvarchar(MAX),
	 EntityType nvarchar(100),
	 EntitySubType nvarchar(100))
	DECLARE @FinalQuery nvarchar(MAX) = '',@AttributeQuery nvarchar(MAX)
	DECLARE @EntityType nvarchar(100),@EntitySubType nvarchar(100)

	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	INSERT INTO #TempTable(EntityID)
	SELECT AllowedEntityID
	FROM dbo.ViewRelatedEntities(@RequestorEntityID,NULL,1,DEFAULT,DEFAULT,DEFAULT,DEFAULT)

	UPDATE TT
	SET TT.EntityType = E.EntityType, TT.EntitySubType = E.EntitySubType
	--SELECT * 
	FROM Entities E INNER JOIN #TempTable TT ON TT.EntityID = E.EntityID


	DECLARE @MyCursor CURSOR
	SET @MyCursor = CURSOR FAST_FORWARD
	FOR 
		SELECT DISTINCT EntityType,EntitySubType FROM #TempTable

	OPEN @MyCursor
	FETCH NEXT FROM @MyCursor
	INTO @EntityType,@EntitySubType
	WHILE @@FETCH_STATUS = 0
	BEGIN
		IF NOT @FinalQuery = ''
			SET @FinalQuery = @FinalQuery + ' UNION '
		EXECUTE ViewEntities @EntityType,@EntitySubType,1,@AttributeQuery OUT
		SET @FinalQuery = @FinalQuery + '(SELECT AQ.EntityID,AQ.Lookup FROM #TempTable TT ' 
						+ 'INNER JOIN (' + @AttributeQuery + ') AQ ON TT.EntityID = AQ.EntityID)'

		FETCH NEXT FROM @MyCursor
		INTO @EntityType,@EntitySubType
	END
	CLOSE @MyCursor
	DEALLOCATE @MyCursor

	IF NOT @OrderBy IS NULL
		SET @FinalQuery = 'SELECT * FROM (' + @FinalQuery + ') AS FQ ORDER BY ' + @OrderBy

	EXECUTE (@FinalQuery)

	--SELECT * FROM #TempTable TT INNER JOIN Entities E ON TT.EntityID = E.EntityID

	DROP TABLE #TempTable
END